﻿using HslCommunication.Core;
using MySql.Data.MySqlClient;
using SYSoft.Comm.DB;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace SYSoft.Comm.Utils
{
    public class MysqlHelper
    {
        #region Contructor

        public MysqlHelper(int i)
        {
            hybirdLock = new SimpleHybirdLock();
            strConnection = BaseDBConfig.ConnectionString;
        }

        public MysqlHelper()
        {
            strConnection = BaseDBConfig.ConnectionString;
        }

        #endregion

        #region Method

        private bool CheckConnection()
        {
            if (isSqlError)
            {
                bool result = false;
                hybirdLock.Enter();
                sqlConnection?.Dispose();
                sqlConnection = new MySqlConnection(strConnection);
                try
                {
                    sqlConnection.Open();
                    isSqlError = false;
                    // logNet?.WriteInfo("MySql Connect Success");
                }
                catch
                {
                    isSqlError = true;
                    //logNet?.WriteFatal("MySql Open Failed");
                }
                finally
                {
                    result = !isSqlError;
                    hybirdLock.Leave();
                }

                return result;
            }
            else
            {
                return true;
            }
        }

        public int ExecuteCommand(string commandStr)
        {
            return ExecuteCommand(new List<string>() { commandStr });
        }

        /// <summary>
        /// 执行多条sql的语句
        /// </summary>
        /// <param name="commandStr">SQL的语句</param>
        /// <returns>是否成功</returns>
        public int ExecuteCommand(List<string> commandStrs)
        {
            if (!CheckConnection()) return 0;

            int count = 0;
            MySqlConnection sqlConnection = null;
            try
            {
                sqlConnection = new MySqlConnection(BaseDBConfig.ConnectionString);
                sqlConnection.Open();
                MySqlCommand sqlCommand = new MySqlCommand("", sqlConnection);
                for (int i = 0; i < commandStrs.Count; i++)
                {
                    sqlCommand.CommandText = commandStrs[i];
                    count += sqlCommand.ExecuteNonQuery();

                }
                isSqlError = false;
                sqlCommand.Dispose();
            }
            catch (Exception ex)
            {
                isSqlError = true;
                count = 0;
                //logNet?.WriteFatal($"Database Wrong Occur:" + ex.Message);
            }
            finally
            {
                sqlConnection.Close();    //关闭数据连接
            }
            return count;
        }

        public  bool ExecuteCommandByTran(List<string> sqlList)
        {
            MySqlConnection sqlConnection = new MySqlConnection(BaseDBConfig.ConnectionString);
            MySqlCommand sqlCommand = new MySqlCommand();
            sqlCommand.Connection = sqlConnection;
            try
            {
                sqlConnection.Open();
                sqlCommand.Transaction = sqlConnection.BeginTransaction();   //开启事务
                foreach (string item in sqlList)
                {
                    sqlCommand.CommandText = item;
                    sqlCommand.ExecuteNonQuery();
                }
                sqlCommand.Transaction.Commit();    //提交事务
                return true;
            }
            catch (Exception ex)
            {
                if (sqlCommand.Transaction != null)
                {
                    sqlCommand.Transaction.Rollback();   //回滚事务
                }
                //throw new Exception(ex.Message);
                return false;
            }
            finally
            {
                if (sqlCommand.Transaction != null)   //事务执行完了清空事务
                {
                    sqlCommand.Transaction = null;
                }
                sqlConnection.Close();
            }
        }



        /// <summary>
        /// MySQL分页
        /// </summary>
        /// <param name="TbName">表名</param>
        /// <param name="StrWhere">条件</param>
        /// <param name="OrderBy">排序语句</param>
        /// <param name="FieldList">查询字段</param>
        /// <param name="CurrentNum">当前页</param>
        /// <param name="PageNum">每页记录数</param>
        /// <param name="AllCount">返回总记录数</param>
        /// <returns></returns>
        public DataTable ExecuteQueryPage(string TbName, string StrWhere, string OrderBy, string FieldList, int CurrentNum, int PageNum, ref int AllCount)
        {
            /*
             * SQL_CALC_FOUND_ROWS sql语句，查看手册后发现此关键词的作用是在查询时统计满足过滤条件后的结果的总数（不受 Limit 的限制），而且FOUND_ROWS() 则取到了这个纪录；
       比如：想查找goods表中k字段为avs的前十条记录，并返回满足这条件的总行数；
       SELECT SQL_CALC_FOUND_ROWS goods WHERE k='avs' LIMIT 10;
       SELECT FOUND_ROWS();
             */
            DataTable dt_MySQL = new DataTable();
            if (CurrentNum < 1)
            {
                CurrentNum = 1;
            }
            Int64 startPageNum = (CurrentNum - 1) * PageNum;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS ");
            sql.Append(FieldList + " FROM " + TbName + " WHERE " + StrWhere);
            sql.Append(" " + OrderBy + " limit " + startPageNum.ToString());
            sql.Append("," + PageNum.ToString());
            sql.Append(" ;SELECT FOUND_ROWS(); ");
            DataSet ds = MySql.Data.MySqlClient.MySqlHelper.ExecuteDataset(BaseDBConfig.ConnectionString, sql.ToString());
            dt_MySQL = ds.Tables[0];
            if (!string.IsNullOrEmpty(ds.Tables[1].Rows[0][0].ToString()))
                AllCount = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
            else
                AllCount = 0;

            return dt_MySQL;
        }






        public DataTable ExecuteQuery(string sql)
        {
            DataTable dt = null;
            MySqlConnection sqlConnection = null;
            try
            {
                sqlConnection = new MySqlConnection(BaseDBConfig.ConnectionString);
                sqlConnection.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(sql, sqlConnection);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                isSqlError = false;
            }
            catch (Exception ex)
            {
                isSqlError = true;

            }
            finally
            {
                sqlConnection.Close();    //关闭数据连接
            }
            return dt;
        }


        #endregion

        #region Private Member

        private MySqlConnection sqlConnection = null;
        private SimpleHybirdLock hybirdLock = null;
        private string strConnection = string.Empty;
        private bool isSqlError = true;
        //private ILogNet logNet = null;

        #endregion
    }
}
